/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sbmart.smartserver.task;

import com.mchange.v2.c3p0.C3P0Registry;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.concurrent.ExecutorService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sbmart.object.*;
import sbmart.smartserver.cache.ConnectionManager;
import sbmart.smartserver.job.ServerMstJob;
import sbmart.smartserver.service.ClientMstService;
import sbmart.smartserver.service.ServerProcedures;
import sbmart.util.Ping;

/**
 *
 * @author albert
 *
 */
public class ServerMstTask implements Runnable {

    private ExecutorService exec;
    public static final Logger LOG = LoggerFactory.getLogger("ServerMst");
    private final List<Header> headers;

    public ServerMstTask(List<Header> headers, ExecutorService exec) {
        this.headers = headers;
        this.exec = exec;
    }

    private Connection getServerConn() throws SQLException {
        LOG.debug("[CALL]");
        Connection conn = C3P0Registry.pooledDataSourceByName("sbmart0").getConnection();
        conn.setAutoCommit(false);
        return conn;
    }

    private void closeServerConn(Connection serverConn) throws SQLException {
        LOG.debug("[CALL]");
        serverConn.close();
    }

    private Connection getClientConn(Header header) throws SQLException {
        LOG.debug("[CALL]");
        Connection conn = ConnectionManager.instance().getClientConnection(header);
        return conn;
    }

    private void closeClientConn(Header header, Connection conn) throws SQLException {
        LOG.debug("[CALL]");
        ConnectionManager.instance().closeClientConnection(header, conn);
    }

    private void rollback(Header header, Connection serverConn, Connection clientConn, SQLException ex) {
        LOG.error("<ERROR> " + header.getBranchId() + "|" + header.getBranchName() + "|" + header.getIpaddress(), ex);
        try {
            if (clientConn != null) {
                clientConn.rollback();
            }
            if (serverConn != null) {
                serverConn.rollback();
            }
            LOG.debug("[ROLLBACK] ", ex);
        } catch (SQLException ex1) {
            LOG.error("[ERROR]", ex1);
        } finally {
        }
    }

    @Override
    public void run() {
        LOG.info("[RUN] " + getClass().getSimpleName());
        Connection serverConn = null, clientConn = null;
        try {
            serverConn = getServerConn();
            if (!serverConn.isClosed()) {
                LOG.debug("[CALL]");
                for (Header header : headers) {
                    putMstSend(header, serverConn);
                }

                for (Header header : headers) {
                    setMstSendTables(header, serverConn);
                }
                serverConn.commit();
                LOG.debug("[COMMIT]");
            }

        } catch (SQLException ex) {
            LOG.error("[ERROR] ROLLBACK putMstSendHistory", ex);
            try {
                if (serverConn != null) {
                    serverConn.rollback();
                }
            } catch (SQLException ex1) {
                LOG.error("[ERROR] ", ex1);
            }
        } catch (Exception e) {
            LOG.error("[ERROR] ", e);
        } finally {
            try {
                if (serverConn != null) {
                    closeServerConn(serverConn);
                }
            } catch (SQLException ex) {
                LOG.error("[ERROR] ", ex);
            }
        }

        // Pointcast to gerai id
        for (Header header : headers) {
            boolean isContinue = false;
            boolean isSend = false;

            LOG.debug(header.getBranchId() + "|" + header.getBranchName() + "|" + header.getIpaddress());
            ServerMstJob.count = 0;
            try {
                serverConn = getServerConn();
                if (serverConn.isClosed()) {
                    LOG.error("[ERROR] connection refuse by server");
                    isContinue = true;
                }



                if (isMstSend(header, serverConn)) {
                    if (Ping.isReachable(header.getIpaddress())) {
                        isSend = true;
                        clientConn = getClientConn(header);
                        if (clientConn == null || clientConn.isClosed()) {
                            LOG.debug("<PASS> InUsed:" + header.getBranchName() + "|" + header.getIpaddress());
                            isContinue = true;
                        }
                        if (!isContinue) {
                            if (serverConn.isClosed()) {
                                serverConn = getServerConn();
                            }

                            if (!serverConn.isClosed() && !clientConn.isClosed()) {
                                getMstMaster(header, serverConn, clientConn);       // Broadcast
                                getMstRelation(header, serverConn, clientConn);     // Broadcast
                                getMstItem(header, serverConn, clientConn);               // Pointcast
                                getMstItemLastCost(header, serverConn, clientConn);       // Pointcast
                                getMstItemVendor(header, serverConn, clientConn);         // Pointcast
                                getMstItemPrice(header, serverConn, clientConn);          // Pointcast
                                getMstItemPricePromo(header, serverConn, clientConn);     // Pointcast        
                                getMstItemDisplay(header, serverConn, clientConn);        // Pointcast
                                getMstModulProduk(header, serverConn, clientConn);        // Pointcast

                                getMstCode(header, serverConn, clientConn);         // Broadcast
                                getMstHadiah(header, serverConn, clientConn);       // Broadcast
                                getMstItemHpp(header, serverConn, clientConn);      // Broadcast
                                getMstItemDiscount(header, serverConn, clientConn); // Broadcast
                                getMstPointMapping(header, serverConn, clientConn); // Broadcast
                                getMstRakSewa(header, serverConn, clientConn);      // Broadcast
                                getMstTenant(header, serverConn, clientConn);       // Broadcast
                                getMstVoucher(header, serverConn, clientConn);      // Broadcast

                                

                            } else {
                                LOG.error("[ERROR] connection refuse or close");
                            }

                        }
                    } else {
                        LOG.error("[ERROR] PING " + header.getBranchName() + "|" + header.getIpaddress());
                    }
                }

            } catch (SQLException ex) {
                LOG.error("[ERROR] Conn Down:" + header.getBranchName() + "|" + header.getIpaddress() + " : " + ex.getMessage());
                try {
                    if (clientConn != null) {
                        clientConn.rollback();
                    }
                    if (serverConn != null) {
                        serverConn.rollback();
                    }
                    LOG.debug("[ROLLBACK] ", ex);
                } catch (SQLException ex1) {
                    LOG.error("[ERROR]", ex1);
                }
            } catch (Exception e) {
                LOG.error("[ERROR] Conn Down:" + header.getBranchName() + "|" + header.getIpaddress());
            } finally {
                try {
                    if (isSend) {
                        closeClientConn(header, clientConn);
                    }

                    if (serverConn != null) {
                        serverConn.close();
                    }
                } catch (SQLException ex) {
                    LOG.error("[ERROR] ", ex);
                }

            }
        }
        LOG.info("[END] " + LOG.getName());

    }

    private boolean isMstSend(Header header, Connection serverConn) {
//        LOG.debug("[CALL]");
        boolean isExists = true;
        try {
            CallableStatement call_is_sendhistory_master_exists = serverConn.prepareCall(ServerProcedures.is_mst_sendhistory_exists);
            call_is_sendhistory_master_exists.setInt("branch_id", header.getBranchId());
            call_is_sendhistory_master_exists.registerOutParameter(1, Types.INTEGER);
            call_is_sendhistory_master_exists.execute();
            int out = call_is_sendhistory_master_exists.getInt(1);
            if (out == 0) {
                isExists = false;
                LOG.debug("<FALSE>");
            } else {
                isExists = true;
                LOG.debug("<TRUE>");
            }
        } catch (SQLException ex) {
            LOG.error("<ERROR>", ex);
        }
        return isExists;
    }

    private void putMstSend(Header header, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL] id: " + header.getBranchId());
        CallableStatement call_put_mst_sendhistory = serverConn.prepareCall(ServerProcedures.put_mst_sendhistory);
        call_put_mst_sendhistory.setInt("branch_id", header.getBranchId());
        call_put_mst_sendhistory.execute();
    }

    private void setMstSendTables(Header header, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL] id: " + header.getBranchId());
        CallableStatement call_set_mst_sendtables = serverConn.prepareCall(ServerProcedures.set_mst_sendtables);
        call_set_mst_sendtables.setInt("branch_id", header.getBranchId());

        call_set_mst_sendtables.execute();
    }

    private void getMstBranch(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_branch = serverConn.prepareCall(ServerProcedures.get_mst_branch);
            call_get_mst_branch.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_branch.executeQuery();
            List<MstBranch> list = new ArrayList<MstBranch>();
            while (rs.next()) {
                MstBranch mst = new MstBranch();
                mst.setBranchId(rs.getShort("branch_id"));
                mst.setBranchName(rs.getString("branch_name"));
                mst.setNickname(rs.getString("nickname"));
                mst.setAddress(rs.getString("address"));
                mst.setAddress2(rs.getString("address2"));
                mst.setCity(rs.getString("city"));
                mst.setPhone(rs.getString("phone"));
                mst.setPhone2(rs.getString("phone2"));
                mst.setFax(rs.getString("fax"));
                mst.setPostCode(rs.getString("post_code"));
                mst.setBranchCode(rs.getString("branch_code"));
                mst.setDeliverAddress(rs.getString("deliver_address"));
                mst.setDeliverName(rs.getString("deliver_name"));

                Calendar cal1 = null;
                if (rs.getDate("disabled_date") != null) {
                    cal1.setTimeInMillis(rs.getDate("disabled_date").getTime());
                }
                mst.setDisabledDate(cal1);

                mst.setEmail(rs.getString("email"));
                mst.setAreaId(rs.getShort("area_id"));
                mst.setTipeRak(rs.getShort("tipe_rak"));
                mst.setOnline(rs.getByte("online"));
                mst.setInsertLog(rs.getString("insert_log"));
                mst.setEditLog(rs.getString("edit_log"));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstBranch(header, list, clientConn);
                clientConn.commit();
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getMstCard(Header header, Connection serverConn, Connection clientConn) throws SQLException {
        //call_get_mst_card = conn.prepareCall(ServerProcedures.get_mst_card);
    }

    private void getMstMaster(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_master = serverConn.prepareCall(ServerProcedures.get_mst_master);
            call_get_mst_master.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_master.executeQuery();
            List<MstMaster> list = new ArrayList<MstMaster>();
            while (rs.next()) {
                MstMaster mst = new MstMaster();
                mst.setMstId(rs.getInt(1));
                mst.setMstTipe(rs.getInt(2));
                mst.setMstCode(rs.getString(3));
                mst.setMstName(rs.getString(4));
                mst.setEditLog(rs.getString(5));
                mst.setInsertLog(rs.getString(6));
                mst.setVers(rs.getLong(7));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstMaster(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    // checked
    private void getMstCode(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_code = serverConn.prepareCall(ServerProcedures.get_mst_code);
            call_get_mst_code.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_code.executeQuery();
            List<MstCode> list = new ArrayList<MstCode>();
            while (rs.next()) {
                MstCode mst = new MstCode();
                mst.setCodeId(rs.getInt(1));
                mst.setCodeCode(rs.getString(2));
                mst.setCodeName(rs.getString(3));
                mst.setParentId(rs.getInt(4));
                mst.setLevel(rs.getInt(5));
                mst.setUrut(rs.getInt(6));
                mst.setEditLog(rs.getString(7));
                mst.setInsertLog(rs.getString(8));
                mst.setVers(rs.getLong(9));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstCode(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    // uncomplete
    private void getMstHadiah(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_hadiah = serverConn.prepareCall(ServerProcedures.get_mst_hadiah);
            call_get_mst_hadiah.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_hadiah.executeQuery();
            List<MstHadiah> list = new ArrayList<MstHadiah>();
            while (rs.next()) {
                MstHadiah mst = new MstHadiah();
                mst.setHadiahId(rs.getInt(1));
                mst.setItemCode(rs.getString(2));
                mst.setItemName(rs.getString(3));
                mst.setDisabledDate(null);
                mst.setInsertLog(rs.getString(4));
                mst.setEditLog(rs.getString(5));
                mst.setVers(rs.getLong(6));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstHadiah(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getMstItem(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_item = serverConn.prepareCall(ServerProcedures.get_mst_item);
            call_get_mst_item.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_item.executeQuery();
            List<MstItem> list = new ArrayList<MstItem>();
            while (rs.next()) {
                MstItem mst = new MstItem();
                mst.setItemId(rs.getInt("item_id"));
                mst.setItemCode(rs.getString("item_code"));
                mst.setBarcode(rs.getString("barcode"));
                mst.setBarcode2(rs.getString("barcode2"));
                mst.setBarcode3(rs.getString("barcode3"));
                mst.setItemName(rs.getString("item_name"));
                mst.setDivisiPo(rs.getString("divisi_po"));
                mst.setStrukName(rs.getString("struk_name"));
                mst.setSpecification(rs.getShort("specification"));
                mst.setBkp(rs.getByte("bkp"));
                mst.setHalal(rs.getByte("halal"));
                mst.setUnit1(rs.getShort("unit1"));
                mst.setUnit2(rs.getShort("unit2"));
                mst.setUnit3(rs.getShort("unit3"));
                mst.setFrac2(rs.getFloat("frac2"));
                mst.setFrac3(rs.getFloat("frac3"));
                mst.setTagPerform(rs.getString("tag_perform"));
                mst.setTagStatus(rs.getString("tag_status"));
                if (rs.getDate("disabled_date") != null) {
                    Calendar cal = Calendar.getInstance();
                    cal.setTimeInMillis(rs.getDate("disabled_date").getTime());
                    mst.setDisabledDate(cal);
                } else {
                    mst.setDisabledDate(null);
                }
                mst.setNpbFraction(rs.getInt("npb_fraction"));
                mst.setGeraiId(rs.getShort("gerai_id"));
                mst.setBkl(rs.getByte("bkl"));
                mst.setBarcode4(rs.getString("barcode4"));
                mst.setEditLog(rs.getString("edit_log"));
                mst.setInsertLog(rs.getString("insert_log"));
                mst.setVers(rs.getLong("version"));
                mst.setItemSewa(rs.getShort("item_sewa"));

                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstItem(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getMstItemDiscount(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_item_discount = serverConn.prepareCall(ServerProcedures.get_mst_item_discount);
            call_get_mst_item_discount.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_item_discount.executeQuery();
            List<MstItemDiscount> list = new ArrayList<MstItemDiscount>();
            while (rs.next()) {
                MstItemDiscount mst = new MstItemDiscount();
                mst.setDiscId(rs.getInt(1));
                mst.setHppId(rs.getInt(2));
                mst.setJenisDisc(rs.getByte(3));
                mst.setRumus(rs.getByte(4));
                if (rs.getDate(5) != null) {
                    Calendar cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate(5).getTime());
                    mst.setPeriodeAwal(cal1);
                } else {
                    mst.setPeriodeAwal(null);
                }

                if (rs.getDate(6) != null) {
                    Calendar cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getDate(6).getTime());
                    mst.setPeriodeAkhir(cal2);
                } else {
                    mst.setPeriodeAkhir(null);
                }

                mst.setDiscount(rs.getBigDecimal(7));
                mst.setIsPercent(rs.getByte(8));
                mst.setFinsert(rs.getByte(9));
                mst.setFupdate(rs.getByte(10));
                mst.setInsertLog(rs.getString(11));
                mst.setEditLog(rs.getString(12));
                mst.setVers(rs.getLong(13));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstItemDiscount(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getMstItemHpp(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_item_hpp = serverConn.prepareCall(ServerProcedures.get_mst_item_hpp);
            call_get_mst_item_hpp.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_item_hpp.executeQuery();
            List<MstItemHpp> list = new ArrayList<MstItemHpp>();
            while (rs.next()) {
                MstItemHpp mst = new MstItemHpp();
                mst.setHppId(rs.getInt("hpp_id"));
                mst.setItemId(rs.getInt("item_id"));
                mst.setVendorId(rs.getShort("vendor_id"));

                Calendar cal1 = null;
                if (rs.getDate("start_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("start_date").getTime());
                }
                mst.setStartDate(cal1);

                mst.setFaction(rs.getInt("faction"));
                mst.setPrice(rs.getFloat("price"));
                mst.setDiscount(rs.getFloat("discount"));
                mst.setTax(rs.getFloat("tax"));
                mst.setPriceCost(rs.getFloat("price_cost"));
                mst.setPkp(rs.getByte("pkp"));
                mst.setKondisiBarang(rs.getByte("kondisi_barang"));
                mst.setTopBuying(rs.getByte("top_buying"));
                mst.setCostAftDisc(rs.getFloat("cost_aft_disc"));
                mst.setCostBfoTax(rs.getFloat("cost_bfo_tax"));
                mst.setCostAftTax(rs.getFloat("cost_aft_tax"));
                mst.setEditLog(rs.getString("edit_log"));
                mst.setInsertLog(rs.getString("insert_log"));
                mst.setVers(rs.getLong("version"));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstItemHpp(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    // checked
    private void getMstItemLastCost(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_item_last_cost = serverConn.prepareCall(ServerProcedures.get_mst_item_last_cost);
            call_get_mst_item_last_cost.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_item_last_cost.executeQuery();
            List<MstItemLastCost> list = new ArrayList<MstItemLastCost>();
            while (rs.next()) {
                MstItemLastCost mst = new MstItemLastCost();
                mst.setItemId(rs.getInt(1));
                mst.setBranchId(rs.getShort(2));
                if (rs.getDate(3) != null) {
                    Calendar cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate(3).getTime());
                    mst.setLastDate(cal1);
                } else {
                    mst.setLastDate(null);
                }
                mst.setLastVendor(rs.getShort(4));
                mst.setLastCost(rs.getFloat(5));
                mst.setLastCostPcs(rs.getFloat(6));
                mst.setTax(rs.getBigDecimal(7));
                mst.setTaxPcs(rs.getBigDecimal(8));
                mst.setLastCostAftTax(rs.getFloat(9));
                mst.setLastCostAftTaxPcs(rs.getFloat(10));
                mst.setAvgCost(rs.getFloat(11));
                mst.setVers(rs.getLong(12));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstItemLastCost(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    // checked
    private void getMstItemDisplay(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_item_display = serverConn.prepareCall(ServerProcedures.get_mst_item_list_display);
            call_get_mst_item_display.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_item_display.executeQuery();
            List<MstItemDisplay> list = new ArrayList<MstItemDisplay>();
            while (rs.next()) {
                MstItemDisplay mst = new MstItemDisplay();
                mst.setDispId(rs.getInt("disp_id"));
                mst.setTipeRak(rs.getShort("tipe_rak"));
                mst.setItemId(rs.getInt("item_id"));
                mst.setRak(rs.getString("rak"));
                mst.setRow(rs.getShort("row"));
                mst.setCol(rs.getShort("col"));
                mst.setLeftRight(rs.getShort("left_right"));
                mst.setUpBottom(rs.getShort("up_bottom"));
                mst.setFrontBack(rs.getShort("front_back"));
                mst.setMaxDisplay(rs.getShort("max_display"));
                mst.setPkm(rs.getInt("pkm"));
                mst.setMinOrder(rs.getInt("min_order"));
                mst.setMpkm(rs.getInt("mpkm"));
                mst.setN1(rs.getInt("n1"));
                mst.setN2(rs.getInt("n2"));
                mst.setN3(rs.getInt("n3"));
                Calendar cal1 = null;
                if (rs.getDate("start_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("start_date").getTime());
                }
                mst.setStartDate(cal1);
                Calendar cal2 = null;
                if (rs.getDate("end_date") != null) {
                    cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getDate("end_date").getTime());
                }
                mst.setEndDate(cal2);
                mst.setInsertLog(rs.getString("insert_log"));
                mst.setEditLog(rs.getString("edit_log"));
                mst.setVers(rs.getLong("version"));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstItemDisplay(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    // checked
    private void getMstItemPrice(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_item_price = serverConn.prepareCall(ServerProcedures.get_mst_item_price);
            call_get_mst_item_price.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_item_price.executeQuery();
            List<MstItemPrice> list = new ArrayList<MstItemPrice>();
            while (rs.next()) {
                MstItemPrice mst = new MstItemPrice();
                mst.setPriceId(rs.getInt("price_id"));
                mst.setPriceLevel(rs.getByte("price_level"));
                mst.setItemId(rs.getInt("item_id"));
                if (rs.getDate("start_date") != null) {
                    Calendar cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("start_date").getTime());
                    mst.setStartDate(cal1);
                } else {
                    mst.setStartDate(null);
                }
                mst.setPriceCost(rs.getFloat("price_cost"));
                mst.setMargin(rs.getFloat("margin"));
                mst.setPrice(rs.getFloat("price"));
                mst.setMarkup(rs.getFloat("markup"));
                mst.setMarkupPct(rs.getFloat("markup_pct"));
                mst.setEditLog(rs.getString("edit_log"));
                mst.setInsertLog(rs.getString("insert_log"));
                mst.setVers(rs.getLong("version"));

                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstItemPriceTmp(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    // checked
    private void getMstItemPricePromo(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_item_price_promo = serverConn.prepareCall(ServerProcedures.get_mst_item_price_promo);
            call_get_mst_item_price_promo.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_item_price_promo.executeQuery();
            List<MstItemPricePromo> list = new ArrayList<MstItemPricePromo>();
            while (rs.next()) {
                MstItemPricePromo mst = new MstItemPricePromo();
                mst.setPriceId(rs.getInt("price_id"));
                mst.setPriceLevel(rs.getByte("price_level"));
                mst.setItemId(rs.getInt("item_id"));

                Calendar cal1 = null;
                if (rs.getDate("start_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("start_date").getTime());
                }
                mst.setStartDate(cal1);

                mst.setPriceCost(rs.getFloat("price_cost"));
                mst.setMargin(rs.getFloat("margin"));
                mst.setPrice(rs.getFloat("price"));
                mst.setMarkup(rs.getFloat("markup"));
                mst.setMarkupPct(rs.getFloat("markup_pct"));
                mst.setEditLog(rs.getString("edit_log"));
                mst.setInsertLog(rs.getString("insert_log"));
                Calendar cal2 = null;
                if (rs.getDate("end_date") != null) {
                    cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getDate("end_date").getTime());
                }
                mst.setEndDate(cal2);
                mst.setVers(rs.getLong("version"));
                mst.setBranchId((short) header.getBranchId());
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstItemPricePromo(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    // checked
    private void getMstItemVendor(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_item_vendor = serverConn.prepareCall(ServerProcedures.get_mst_item_vendor);
            call_get_mst_item_vendor.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_item_vendor.executeQuery();
            List<MstItemVendor> list = new ArrayList<MstItemVendor>();
            while (rs.next()) {
                MstItemVendor mst = new MstItemVendor();

                mst.setItemVendorId(rs.getInt(1));
                mst.setItemId(rs.getInt(2));
                mst.setVendorId(rs.getShort(3));
                mst.setBranchId(rs.getShort(4));
                mst.setAktif(rs.getByte(5));

                Calendar cal1 = null;
                if (rs.getDate(6) != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate(6).getTime());
                }
                mst.setInsertDate(cal1);

                mst.setEditLog(rs.getString(7));
                mst.setInsertLog(rs.getString(8));
                mst.setBkl(rs.getShort(9));
                mst.setVers(rs.getLong(10));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstItemVendor(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getMstModulProduk(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_modul_produk = serverConn.prepareCall(ServerProcedures.get_mst_modul_produk);
            call_get_mst_modul_produk.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_modul_produk.executeQuery();
            List<MstModulProduk> list = new ArrayList<MstModulProduk>();
            while (rs.next()) {
                MstModulProduk mst = new MstModulProduk();
                mst.setModulId(rs.getInt("modul_id"));
                mst.setJenisModul(rs.getByte("jenis_modul"));
                if (rs.getDate("periode_awal") != null) {
                    Calendar cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("periode_awal").getTime());
                    mst.setPeriodeAwal(cal1);
                } else {
                    mst.setPeriodeAwal(null);
                }
                if (rs.getDate("periode_akhir") != null) {
                    Calendar cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getDate("periode_akhir").getTime());
                    mst.setPeriodeAkhir(cal2);
                } else {
                    mst.setPeriodeAkhir(null);
                }
                mst.setJenisProduk(rs.getByte("jenis_produk"));
                mst.setProdukId(rs.getInt("produk_id"));
                mst.setHadiahId(rs.getInt("hadiah_id"));
                mst.setBatas(rs.getFloat("batas"));
                mst.setNilaiHadiah(rs.getFloat("nilai_hadiah"));
                mst.setBatasTambahan(rs.getFloat("batas_tambahan"));
                mst.setNilaiTambahan(rs.getFloat("nilai_tambahan"));
                mst.setFinsert(rs.getByte("finsert"));
                mst.setFupdate(rs.getByte("fupdate"));
                mst.setInsertLog(rs.getString("insert_log"));
                mst.setEditLog(rs.getString("edit_log"));
                mst.setTipe(rs.getShort("tipe"));
                mst.setCalcTipe(rs.getString("calc_tipe"));
                mst.setKelipatan(rs.getByte("kelipatan"));
                mst.setBatasMax(rs.getFloat("batas_max"));
                mst.setNote(rs.getString("note"));
                mst.setVers(rs.getLong("version"));
                mst.setNilaiAnggota(rs.getFloat("nilai_anggota"));
                mst.setCardId(rs.getInt("card_id"));
                mst.setBatasCard(rs.getFloat("batas_card"));
                mst.setNilaiCard(rs.getFloat("nilai_card"));
                list.add(mst);

            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstModulProduct(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    // checked
    private void getMstPointMapping(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_point_mapping = serverConn.prepareCall(ServerProcedures.get_mst_point_mapping);
            call_get_mst_point_mapping.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_point_mapping.executeQuery();
            List<MstPointMaping> list = new ArrayList<MstPointMaping>();
            while (rs.next()) {
                MstPointMaping mst = new MstPointMaping();
                mst.setCodeId(rs.getInt(1));
                mst.setRootId(rs.getInt(2));
                java.sql.Date date = rs.getDate(3);
                Calendar cal1 = null;
                if (date != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(date.getTime());
                }
                mst.setEditLog(cal1);
                mst.setVers(rs.getLong(4));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstPointMapping(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getMstRakSewa(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_rak_sewa = serverConn.prepareCall(ServerProcedures.get_mst_rak_sewa);
            call_get_mst_rak_sewa.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_rak_sewa.executeQuery();
            List<MstRakSewa> list = new ArrayList<MstRakSewa>();
            while (rs.next()) {
                MstRakSewa mst = new MstRakSewa();
                mst.setRakId(rs.getByte(1));
                mst.setRakKode(rs.getString(2));
                mst.setRakNama(rs.getString(3));
                mst.setRakType(rs.getString(4));
                mst.setInsertLog(rs.getString(5));
                mst.setEditLog(rs.getString(6));
                mst.setVers(rs.getLong(7));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstRakSewa(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getMstRelation(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_relation = serverConn.prepareCall(ServerProcedures.get_mst_relation);
            call_get_mst_relation.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_relation.executeQuery();
            List<MstRelation> list = new ArrayList<MstRelation>();
            while (rs.next()) {
                MstRelation mst = new MstRelation();
                mst.setRelationId(rs.getInt(1));
                mst.setRelationCode(rs.getString(2));
                mst.setRelationName(rs.getString(3));
                mst.setAddress(rs.getString(4));
                mst.setCity(rs.getString(5));
                mst.setPostCode(rs.getString(6));
                mst.setPhone(rs.getString(7));
                mst.setFax(rs.getString(8));
                mst.setContactPerson(rs.getString(9));
                mst.setNpwp(rs.getString(10));
                mst.setNpwpName(rs.getString(11));
                mst.setNpwpAddress(rs.getString(12));
                mst.setSkp(rs.getString(13));
                if (rs.getDate(14) != null) {
                    Calendar cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate(14).getTime());
                    mst.setSkpDate(cal1);
                } else {
                    mst.setSkpDate(null);
                }
                mst.setJadwalOrder(rs.getString(15));
                mst.setLeadTime(rs.getByte(16));
                mst.setTermOfPayment(rs.getShort(17));
                mst.setOpenTop(rs.getByte(18));
                mst.setKondisiBarang(rs.getByte(19));
                mst.setJenisPajak(rs.getByte(20));
                mst.setEmail(rs.getString(21));
                mst.setWebsite(rs.getString(22));
                mst.setCurrentDebt(rs.getFloat(23));
                mst.setHoldOrder(rs.getByte(24));
                mst.setAcName(rs.getString(25));
                mst.setBankName(rs.getString(26));
                mst.setAccId(rs.getString(27));

                if (rs.getDate(28) != null) {
                    Calendar cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getDate(28).getTime());
                    mst.setDisabledDate(cal2);
                } else {
                    mst.setDisabledDate(null);
                }
                mst.setEditLog(rs.getString(29));
                mst.setInsertLog(rs.getString(30));
                mst.setVers(rs.getLong(31));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstRelation(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }
// checked

    private void getMstTenant(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_tenant = serverConn.prepareCall(ServerProcedures.get_mst_tenant);
            call_get_mst_tenant.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_tenant.executeQuery();
            List<MstTenant> list = new ArrayList<MstTenant>();
            while (rs.next()) {
                MstTenant mst = new MstTenant();
                mst.setTenantId(rs.getShort(1));
                mst.setTenantCode(rs.getString(2));
                mst.setTenantName(rs.getString(3));
                mst.setAddress(rs.getString(4));
                mst.setAddressLast(rs.getString(5));
                mst.setKtp(rs.getString(6));
                mst.setPhone(rs.getString(7));
                mst.setHp(rs.getString(8));
                mst.setUsahaJenis(rs.getString(9));
                mst.setUsahaHasilHarian(rs.getFloat(10));
                mst.setUsahaJmlPembeli(rs.getShort(11));
                mst.setPeralatanDimensi(rs.getShort(12));
                mst.setPeralatanOther(rs.getString(13));
                mst.setPeralatanPanjang(rs.getShort(14));
                mst.setPeralatanLebar(rs.getShort(15));
                mst.setPeralatanTinggi(rs.getShort(16));
                mst.setLabaPerhari(rs.getFloat(17));
                mst.setBiayaBahanPokok(rs.getFloat(18));
                mst.setBiayaOperasional(rs.getFloat(19));
                mst.setBiayaLain(rs.getFloat(20));
                mst.setPendatapanBulan(rs.getFloat(21));
                if (rs.getDate(22) != null) {
                    Calendar cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate(22).getTime());
                    mst.setDisabledDate(cal1);
                } else {
                    mst.setDisabledDate(null);
                }
                mst.setInsertLog(rs.getString(23));
                mst.setEditLog(rs.getString(24));
                mst.setNpwp(rs.getString(25));
                mst.setIsFirst(rs.getByte(26));
                mst.setVers(rs.getLong(27));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstTenant(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    // checked
    private void getMstVoucher(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_mst_voucher = serverConn.prepareCall(ServerProcedures.get_mst_voucher);
            call_get_mst_voucher.setInt("branch_id", header.getBranchId());

            ResultSet rs = call_get_mst_voucher.executeQuery();
            List<MstVoucher> list = new ArrayList<MstVoucher>();
            while (rs.next()) {
                MstVoucher mst = new MstVoucher();
                mst.setVoucherId(rs.getInt("voucher_id"));
                mst.setVoucherCode(rs.getString("voucher_code"));
                mst.setAmount(rs.getFloat("amount"));

                if (rs.getDate("expired_date") != null) {
                    Calendar cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("expired_date").getTime());
                    mst.setExpiredDate(cal1);
                } else {
                    mst.setExpiredDate(null);
                }

                if (rs.getDate("disabled_date") != null) {
                    Calendar cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getDate("disabled_date").getTime());
                    mst.setDisabledDate(cal2);
                } else {
                    mst.setDisabledDate(null);
                }

                mst.setFinsert(rs.getByte("finsert"));
                mst.setFupdate(rs.getByte("fupdate"));
                mst.setNotes(rs.getString("notes"));
                mst.setUsed(rs.getByte("used"));

                if (rs.getDate("used_date") != null) {
                    Calendar cal3 = Calendar.getInstance();
                    cal3.setTimeInMillis(rs.getDate("used_date").getTime());
                    mst.setUsedDate(cal3);
                } else {
                    mst.setUsedDate(null);
                }
                mst.setBranchId(rs.getShort("branch_id"));
                mst.setEditLog(rs.getString("edit_log"));
                mst.setInsertLog(rs.getString("insert_log"));
                mst.setVers(rs.getLong("version"));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientMstService.instance().putMstVoucher(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

}
